/**
 * 
 */
package plug.bookmarks;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import com.uujava.tools.Str;

/**
 * 完成对Bean的处理
 * @author k 2013年8月12日 14:59:49
 *
 */
public class BeanCL {
	private Statement sm=null;
	private ResultSet rs=null;
	private Connection ct=null;
	private Logger logger=Logger.getLogger(BeanCL.class);
	public boolean addBookmark(String url,String title,String subgroup){
		ct=new ConnDB().getConn();//获得连接
		if(ct==null)return false;
		
		StringBuffer sql;
		if(subgroup==null){
			sql=Str.bufferAppend("INSERT INTO BOOKMARKS(URL,TITLE)  VALUES('",url,"','",subgroup,"')");
		}else {
			sql=Str.bufferAppend("INSERT INTO BOOKMARKS(URL,TITLE,SUBGROUP)  VALUES('",url,"','",title,"','",subgroup,"')");
		}
		
		logger.debug("addBookmark sql:"+sql.toString());
		//3创建Statement
		try {
			sm=ct.createStatement();
			sm.executeUpdate(sql.toString());//插入
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.debug("添加书签失败");
			e.printStackTrace();
			return false;
		}finally{
			close();
		}
		return true;
	}
	/**
	 * 功能：删除书签
	 * @author k 2013年8月12日 16:02:48
	 */
	public boolean delBookmark(String url) {
		ct = new ConnDB().getConn();// 获得连接
		if (ct == null)
				return false;
		String sql=Str.bufferAppend("DELETE FROM bookmarks WHERE URL='",url,"'").toString();
		try {
			sm = ct.createStatement();
			
			/*StringBuffer sql = new StringBuffer(
					"DELETE FROM bookmarks WHERE URL='");
			sql.append(url).append("'");*/
			sm.executeUpdate(sql);// 删除
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close();
		}

		return true;
	}
	public ArrayList<Bean> selectBookmarks(String subgroup) {
		ct = new ConnDB().getConn();// 获得连接
		if (ct == null)
			return null;
		String sql=Str.bufferAppend("select * from bookmarks where subgroup ='",subgroup,"'  ORDER BY  use_frequency DESC;").toString();
		// select * from bookmarks where subgroup ='默认收藏' ORDER BY use_frequency
		// DESC;
		ArrayList<Bean> array=new ArrayList<Bean>();
		ResultSet rset;
		try {
			sm = ct.createStatement();
			rset = sm.executeQuery(sql);// 查询
			sql=null;
			Bean urlBean;
			while (rset.next()) {
				urlBean=new Bean();
				urlBean.url=rset.getString("url");
				urlBean.title=rset.getString("title");
				urlBean.icon=rset.getString("icon");
				urlBean.annotation=rset.getString("annotation");
				urlBean.add_date=rset.getTime("add_date");
				urlBean.use_frequency=rset.getInt("use_frequency");
				urlBean.subgroup=rset.getString("subgroup");
				array.add(urlBean);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.debug("查询组书签错误");
			e.printStackTrace();
		} finally {
			close();
		}

		return array;
	}
	
	//查询所有分组
	public ArrayList<String> selectSubgroup(){
		ArrayList<String> array=new ArrayList<String>();
		ct = new ConnDB().getConn();// 获得连接
		try {
			sm = ct.createStatement();
			if (ct == null)
				return null;
			ResultSet rset;
			rset =sm.executeQuery("select distinct subgroup from bookmarks");//查询
			while (rset.next()) {
				array.add(rset.getString("subgroup"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			logger.debug("获得所有分组title出错！");
			e.printStackTrace();
		}finally{
			close();
		}

		
		return array;
	}
	
	//关闭资源
	private void close(){
		try {
			if(rs!=null){
				rs.close();
				rs=null;
			}
			if(sm!=null){
				sm.close();
				sm=null;
			}
			if(ct!=null){
				ct.close();
				ct=null;
			}
		} catch (Exception e2) {
			// TODO: handle exception
			logger.debug("关闭资源出错！");
			e2.printStackTrace();
		}
	}

}
